﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeCamp12.Domain;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using log4net;

namespace CodeCamp12.Domain
{
    public class SponsorDataManagement
    {
        private ConnectionStringProvider connString = new ConnectionStringProvider();
        private ILog log = LogManager.GetLogger("CodeCamp12.DAL");

        /// <summary>
        /// Gets all sponsor types from the database
        /// </summary>
        /// <returns>SponsorType</returns>
        public List<SponsorType> GetAllSponsorTypes()
        {
            List<SponsorType> sponsorList = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAllSponsorTypes", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};

            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if(sponsorList == null)
                    {
                        sponsorList = new List<SponsorType>();
                    }
                    var sponsorType = new SponsorType();
                    sponsorType.ID = new Guid(reader["ID"].ToString());
                    sponsorType.Name = reader["Name"].ToString();
                    sponsorList.Add(sponsorType);
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return sponsorList;
        }
        
        /// <summary>
        /// Gets all sponsors from the database
        /// </summary>
        /// <returns>Sponsor</returns>
        public List<Sponsor> GetAllSponsors()
        {
            List<Sponsor> sponsorList = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAllSponsors", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    if(sponsorList == null)
                    {
                        sponsorList = new List<Sponsor>();
                    }
                    var sponsor = new Sponsor
                                      {
                                          ID = new Guid(reader["ID"].ToString()),
                                          SponsorName = reader["SponsorName"].ToString(),
                                          SponsorType = new Guid(reader["SponsorType"].ToString()),
                                          LinkToWebSite = reader["LinkToWebSite"].ToString(),
                                          SponsorImage = new MemoryStream((byte[]) reader["SponsorImage"]),
                                          SponsorTypeText = reader["Name"].ToString()
                                      };
                    sponsorList.Add(sponsor);
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return sponsorList;
        }
        
        /// <summary>
        /// Inserts sponsor in the database
        /// </summary>
        /// <param name="sponsor"></param>
        public void InsertSponsor(Sponsor sponsor)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("InsertSponsor", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@SponsorName", sponsor.SponsorName);
            comm.Parameters.AddWithValue("@SponsorType", sponsor.SponsorType);
            comm.Parameters.AddWithValue("@LinkToWebSite", sponsor.LinkToWebSite);
            var documentBinary = new byte[sponsor.SponsorImage.Length];
            sponsor.SponsorImage.Read(documentBinary, 0, Convert.ToInt32(sponsor.SponsorImage.Length));
            comm.Parameters.AddWithValue("@SponsorImage", documentBinary);
            
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        
        /// <summary>
        /// Updates existing sponsor in the database
        /// </summary>
        /// <param name="sponsor"></param>
        public void UpdateSponsor(Sponsor sponsor)
        {
            var conn = connString.GetConnection();
            SqlCommand comm;
            if (sponsor.SponsorImage == null)
                comm = new SqlCommand("UpdateSponsorNoImage", conn);
            else
            {
                comm = new SqlCommand("UpdateSponsor", conn);
                var documentBinary = new byte[sponsor.SponsorImage.Length];
                sponsor.SponsorImage.Read(documentBinary, 0, Convert.ToInt32(sponsor.SponsorImage.Length));
                comm.Parameters.AddWithValue("@SponsorImage", documentBinary);
            }

            comm.CommandType = System.Data.CommandType.StoredProcedure;
            comm.Parameters.AddWithValue("@ID", sponsor.ID);
            comm.Parameters.AddWithValue("@SponsorName", sponsor.SponsorName);
            comm.Parameters.AddWithValue("@SponsorType", sponsor.SponsorType);
            comm.Parameters.AddWithValue("@LinkToWebSite", sponsor.LinkToWebSite);
            
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Deletes sponsor from database
        /// </summary>
        /// <param name="sponsor"></param>
        public void DeleteSponsor(Sponsor sponsor)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("DeleteSponsor", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", sponsor.ID);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }
        
        /// <summary>
        /// Gets sponsor with the id passed as parametar
        /// </summary>
        /// <param name="sponsorId"></param>
        /// <returns>Sponsor</returns>
        public Sponsor GetSponsorById(Guid sponsorId)
        {
            Sponsor sponsor = null;
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetSponsorByID", conn) {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id",sponsorId);
            try
            {
                conn.Open();
                SqlDataReader reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    sponsor = new Sponsor();
                    sponsor.ID = new Guid(reader["ID"].ToString());
                    sponsor.SponsorName = reader["SponsorName"].ToString();
                    sponsor.SponsorType = new Guid(reader["SponsorType"].ToString());
                    sponsor.LinkToWebSite = reader["LinkToWebSite"].ToString();
                    sponsor.SponsorImage = new MemoryStream((byte[])reader["SponsorImage"]);
                }
                reader.Close();
            }
            catch(Exception ex)
            {
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return sponsor;
        }   
    }
}
